package com.ce.common.db.execution;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;

import java.sql.ResultSet;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.ce.common.db.base.DBConnection;
import com.ce.common.db.base.Database;
import com.ce.common.db.base.JDBCConnection;
import com.ce.common.db.base.PropertiesUtil;
import com.ce.common.db.execution.SQLFileExecution;
import com.ce.common.db.execution.impl.MySQLFileExecution;

/**
 * 测试数据库操作类
 * @author yangenxiong
 *
 */
public class SQLFileExecutionT {
	
	SQLFileExecution handler;
	
	DBConnection conn;
	
	Database db;
	
	@Before
	public void setUp() throws Exception {
		db = new Database(PropertiesUtil.JDBC_USERNAME, 
				PropertiesUtil.JDBC_PASSWD, PropertiesUtil.JDBC_DATABASE, "localhost", "");
		handler = new MySQLFileExecution(db);
		conn = JDBCConnection.getJDBCConnection();
		
		
		handler.executeFile("src/test/resources/test_init.sql");
		Thread.sleep(15000);
		
		conn.execute("SET FOREIGN_KEY_CHECKS=0;");
	}
	
	@After
	public void tearDown() throws Exception {
		conn.execute("SET FOREIGN_KEY_CHECKS=1;");
		handler = null;
		db = null;
		conn = null;
	}
	
	/**
	 * 测试执行SQL文件方法
	 * @anthor yangenxiong
	 */
	@Test
	public void testExecuteFile() throws Exception {
		
		
		//将原来SYS_DATA_DICTIONARY的数据清空
		boolean clean = conn.execute("delete from SYS_DATA_DICTIONARY");
		assertEquals(clean, false);
		
		//再查询SYS_DATA_DICTIONARY表, 并断言里面没有数据
		ResultSet rs = conn.query("select * from SYS_DATA_DICTIONARY");
		assertEquals(rs.next(), false);
		conn.closeResultSet(rs);
		
		//执行写入SYS_DATA_DICTIONARY的文件
		boolean result = handler.executeFile("src/test/resources/execution/executeFile.sql");
		assertEquals(result, true);
		
		//执行需要时间, 等待10秒
		Thread.sleep(10000);
		
		//查询SYS_DATA_DICTIONARY表, 并断言里面有数据, 数据已经被写入
		ResultSet rs2 = conn.query("select * from SYS_DATA_DICTIONARY");
		assertEquals(rs2.next(), true);
		
		conn.closeResultSet(rs2);
		
		//为了不影响其他测试, 将写入的数据删除
		boolean flag = conn.execute("delete from SYS_DATA_DICTIONARY");
		assertEquals(flag, false);
		
	}
	
	/**
	 * 测试执行多份SQL文件方法
	 * @anthor yangenxiong
	 */
	@Test
	public void testExecuteFiles() throws Exception {
		//将`SYS_SET_MENUITEM`数据清空, 先将原来的数据清空
		boolean flag1 = conn.execute("delete from SYS_SET_MENUITEM");
		assertEquals(flag1, false);
		
		//将原来SYS_DATA_DICTIONARY的数据清空
		boolean flag2 = conn.execute("delete from SYS_DATA_DICTIONARY");
		assertEquals(flag2, false);
		
		//SYS_SET_MENUITEM表中的数据已经清空
		ResultSet beforeExecute1 = conn.query("select * from SYS_SET_MENUITEM");
		assertEquals(beforeExecute1.next(), false);//断言SYS_SET_MENUITEM表中没有数据
		conn.closeResultSet(beforeExecute1);
		//SYS_DATA_DICTIONARY已经清空没有数据
		ResultSet beforeExecute2 = conn.query("select * from SYS_DATA_DICTIONARY");
		assertEquals(beforeExecute2.next(), false);//断言SYS_DATA_DICTIONARY表中有数据
		conn.closeResultSet(beforeExecute2);
		
		/**
		 * 执行src/test/resources/execution/executeFiles1.sql插入SYS_SET_MENUITEM表数据
		 * 执行src/test/resources/execution/executeFiles2.sql插入SYS_DATA_DICTIONARY表数据
		 */
		boolean result = handler.executeFiles(new String[]{"src/test/resources/execution/executeFiles1.sql", 
				"src/test/resources/execution/executeFiles2.sql"});
		assertEquals(result, true);
		
		Thread.sleep(5000);
		
		//再对两个表进行查询, 断言两个中有数据
		ResultSet afterExecute1 = conn.query("select * from SYS_SET_MENUITEM");
		assertEquals(afterExecute1.next(), true);
		conn.closeResultSet(afterExecute1);
		
		ResultSet afterExecute2 = conn.query("select * from SYS_DATA_DICTIONARY");
		assertEquals(afterExecute2.next(), true);
		conn.closeResultSet(afterExecute2);
		
		//再清空两表数据
		flag1 = conn.execute("delete from SYS_SET_MENUITEM");
		assertEquals(flag1, false);
		
		flag2 = conn.execute("delete from SYS_DATA_DICTIONARY");
		assertEquals(flag2, false);
	}
}
